{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 采集公众号"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "'''\n",
    "阿里研究院\n",
    "阿里健康\n",
    "阿里巴巴商学院\n",
    "阿里数据\n",
    "\n",
    "腾讯金融科技\n",
    "腾讯研究院\n",
    "腾讯媒体研究院\n",
    "腾讯云启研究院\n",
    "酷鹅用户研究院\n",
    "'''\n",
    "公众号 = \"蚂蚁金服科技\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "fn = { \"output\" : { \"公众号_htm_snippets\": \"data_raw_src/公众号_htm_snippets_{公众号}.tsv\",\n",
    "                    \"公众号_df\": \"data_raw_src/公众号_df_{公众号}.tsv\",\n",
    "                    \"公众号_xlsx\": \"data_sets/公众号_url_{公众号}.xlsx\" } \\\n",
    "      }"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "from lxml.html import fromstring\n",
    "import time\n",
    "from random import random\n",
    "\n",
    "# when selenium main_content is used\n",
    "# Parses an HTML document from a string constant.  Returns the root nood\n",
    "# root = fromstring(df.loc[1,\"html_snippets\"]) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 使用Selenium\n",
    "* 要更改 opts.binary_location 至自己本地的Chrome浏览器，建议portable\n",
    "* Chrome浏览器 和 chromedriver.exe要同版本号到小数后一位\n",
    "* 要确保可以 开启浏览器机器人\n",
    "* 要确保浏览器机器人 可以打开网页 driver.get(\"https://mp.weixin.qq.com\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "ename": "ModuleNotFoundError",
     "evalue": "No module named 'selenium'",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mModuleNotFoundError\u001b[0m                       Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-4-e0e23b95d00a>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m()\u001b[0m\n\u001b[1;32m----> 1\u001b[1;33m \u001b[1;32mfrom\u001b[0m \u001b[0mselenium\u001b[0m \u001b[1;32mimport\u001b[0m \u001b[0mwebdriver\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      2\u001b[0m \u001b[1;32mfrom\u001b[0m \u001b[0mselenium\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mwebdriver\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcommon\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mdesired_capabilities\u001b[0m \u001b[1;32mimport\u001b[0m \u001b[0mDesiredCapabilities\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      3\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      4\u001b[0m \u001b[1;31m#caps=dict()\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      5\u001b[0m \u001b[1;31m#caps[\"pageLoadStrategy\"] = \"none\"   # Do not wait for full page load\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mModuleNotFoundError\u001b[0m: No module named 'selenium'"
     ]
    }
   ],
   "source": [
    "from selenium import webdriver\n",
    "from selenium.webdriver.common.desired_capabilities import DesiredCapabilities\n",
    "\n",
    "#caps=dict()\n",
    "#caps[\"pageLoadStrategy\"] = \"none\"   # Do not wait for full page load\n",
    "\n",
    "opts = webdriver.ChromeOptions()\n",
    "opts.add_argument('--no-sandbox')#解决DevToolsActivePort文件不存在的报错\n",
    "opts.add_argument('window-size=1920x3000') #指定浏览器分辨率\n",
    "opts.add_argument('--disable-gpu') #谷歌文档提到需要加上一这个属性来规避bug\n",
    "opts.add_argument('--hide-scrollbars') #隐藏滚动条, 应对些特殊页面\n",
    "#opts.add_argument('blink-settings=imagesEnabled=false') #不加载图片, 提升速度\n",
    "#opts.add_argument('--headless') #浏览器不提供可视化页面. linux下如果系统不支持可视化不加这条会启动失败\n",
    "\n",
    "opts.binary_location = \"C:\\portable\\PortableApps\\IronPortable\\App\\Iron\\chrome.exe\" #\"H:\\_coding_\\Gitee\\InternetNewMedia\\CapstonePrj2016\\chromedriver.exe\"  \n",
    "\n",
    "# \"H:\\_coding_\\Gitee\\InternetNewMedia\\CapstonePrj2016\\chromedriver.exe\"  \n",
    "driver = webdriver.Chrome( chrome_options = opts) #desired_capabilities=caps,"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 272,
   "metadata": {},
   "outputs": [],
   "source": [
    "driver.get(\"https://mp.weixin.qq.com\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 填表登入"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 276,
   "metadata": {},
   "outputs": [],
   "source": [
    "payload =  {\"account\": \"lujiayan123@126.com\", \"password\": \"Aa86460123\"}\n",
    "# payload =  {\"account\": \"NFUHacks@163.com\", \"password\": \"NFU706947580\"}\n",
    "driver.find_element_by_xpath('//div[@class=\"login__type__container login__type__container__scan\"]/a').click()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 277,
   "metadata": {},
   "outputs": [],
   "source": [
    "driver.find_element_by_xpath('//form[@class=\"login_form\"]//input[@name=\"account\"]').clear()\n",
    "driver.find_element_by_xpath('//form[@class=\"login_form\"]//input[@name=\"account\"]').send_keys(payload['account'])\n",
    "driver.find_element_by_xpath('//form[@class=\"login_form\"]//input[@name=\"password\"]').clear()\n",
    "driver.find_element_by_xpath('//form[@class=\"login_form\"]//input[@name=\"password\"]').send_keys(payload['password'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 278,
   "metadata": {},
   "outputs": [],
   "source": [
    "driver.find_element_by_xpath('//div[@class=\"login_btn_panel\"]/a').click()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 点选单"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 279,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'展开'"
      ]
     },
     "execution_count": 279,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "element = driver.find_element_by_xpath('//a[@id=\"m_open\"]')\n",
    "element.click()\n",
    "main_content = element.get_attribute('innerHTML')\n",
    "main_content"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 280,
   "metadata": {},
   "outputs": [],
   "source": [
    "driver.execute_script(\"window.scrollTo(0,document.body.scrollHeight)\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 281,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'https://mp.weixin.qq.com/cgi-bin/appmsg?begin=0&count=10&t=media/appmsg_list&type=10&action=list&token=147175892&lang=zh_CN'"
      ]
     },
     "execution_count": 281,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "element = driver.find_element_by_xpath('//li[@title[contains(.,\"素材管理\")]]/a') \n",
    "# main_content = element.get_attribute('innerHTML')\n",
    "# main_content\n",
    "url2= element.get_attribute(\"href\")\n",
    "url2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 282,
   "metadata": {},
   "outputs": [],
   "source": [
    "driver.get(url2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 新建图文消息"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 283,
   "metadata": {},
   "outputs": [],
   "source": [
    "element = driver.find_element_by_xpath('//*[text()[contains(.,\"新建图文消息\")]]') \n",
    "main_content = element.get_attribute('innerHTML')\n",
    "main_content\n",
    "element.click()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 284,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['CDwindow-37765C5379860D7649D545B745023AF8', 'CDwindow-D69045029FCF892CCA2091A05F3BBC17']\n"
     ]
    }
   ],
   "source": [
    "print (driver.window_handles)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 285,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 新建图文消息开了另一分视窗，所以要切换 switch_to \n",
    "driver.switch_to.window(driver.window_handles[-1])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 超链接"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 450,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                超链接              \n"
     ]
    }
   ],
   "source": [
    "element = driver.find_element_by_xpath('//*[text()[contains(.,\"超链接\")]]') \n",
    "main_content = element.get_attribute('innerHTML')\n",
    "print(main_content)\n",
    "element.click()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 451,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "选择其他公众号\n"
     ]
    }
   ],
   "source": [
    "# 点 选择其他公众号\n",
    "element = driver.find_element_by_xpath('//*[text()[contains(.,\"选择其他公众号\")]]') \n",
    "main_content = element.get_attribute('innerHTML')\n",
    "print(main_content)\n",
    "element.click()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 452,
   "metadata": {},
   "outputs": [],
   "source": [
    "driver.find_element_by_xpath('//form//div[@class=\"inner_link_account_area\"]//input[@class=\"weui-desktop-form__input\"]').clear()\n",
    "driver.find_element_by_xpath('//form//div[@class=\"inner_link_account_area\"]//input[@class=\"weui-desktop-form__input\"]').send_keys(公众号)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 453,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<div class=\"weui-desktop-icon weui-desktop-icon__inputSearch weui-desktop-icon__small\"><!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <svg width=\"16\" height=\"16\" viewBox=\"0 0 16 16\" xmlns=\"http://www.w3.org/2000/svg\"><path d=\"M11.33 10.007l4.273 4.273a.502.502 0 0 1 .005.709l-.585.584a.499.499 0 0 1-.709-.004L10.046 11.3a6.278 6.278 0 1 1 1.284-1.294zm.012-3.729a5.063 5.063 0 1 0-10.127 0 5.063 5.063 0 0 0 10.127 0z\"></path></svg> <!----> <!----> <!----> <!----></div>\n"
     ]
    }
   ],
   "source": [
    "# 点放大镜搜\n",
    "element = driver.find_element_by_xpath('//button[@class=\"weui-desktop-icon-btn weui-desktop-search__btn\"]')\n",
    "main_content = element.get_attribute('innerHTML')\n",
    "print(main_content)\n",
    "element.click()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 454,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<li class=\"inner_link_account_item\"><div class=\"weui-desktop-vm_primary\"><img src=\"http://mmbiz.qpic.cn/mmbiz_png/8zGb2W5DJWvF1ibic8Ff0hib2zM1PNggypmobkam18vP1Nh7kaBU77zrJ8WD9vd8nj4uicvXIFXu1Ek0ScZGia91Clw/0?wx_fmt=png\" class=\"inner_link_account_avatar\"> <strong class=\"inner_link_account_nickname\">蚂蚁金服科技</strong> <i class=\"inner_link_account_wechat\">微信号：Ant-Techfin</i></div> <div class=\"weui-desktop-vm_default inner_link_account_type\">订阅号</div></li><li class=\"inner_link_account_item\"><div class=\"weui-desktop-vm_primary\"><img src=\"http://mmbiz.qpic.cn/mmbiz_png/rS76otFH2pNlicVQBHK655aKVDMOs2fbSdQb0Zxic4ibIGJUNAayAjy3yCeWqSFe4GVNhExtpNt4opiavkicHGHI1hQ/0?wx_fmt=png\" class=\"inner_link_account_avatar\"> <strong class=\"inner_link_account_nickname\">蚂蚁金服</strong> <i class=\"inner_link_account_wechat\">微信号：mayi-jinfu</i></div> <div class=\"weui-desktop-vm_default inner_link_account_type\">订阅号</div></li><li class=\"inner_link_account_item\"><div class=\"weui-desktop-vm_primary\"><img src=\"http://mmbiz.qpic.cn/mmbiz_png/ibouibdiaMA9KyndRqTmA3nJJpuL3eCmHqF9L5FzIQ3aoG8fH3gZT8dT3uZyD9YYvA9dlwTsHDfEtgW9hVt5jKHkg/0?wx_fmt=png\" class=\"inner_link_account_avatar\"> <strong class=\"inner_link_account_nickname\">山东蚂蚁金服网络科技有限公司</strong> <i class=\"inner_link_account_wechat\">微信号：SDMYJF_FWH</i></div> <div class=\"weui-desktop-vm_default inner_link_account_type\">服务号</div></li><li class=\"inner_link_account_item\"><div class=\"weui-desktop-vm_primary\"><img src=\"http://mmbiz.qpic.cn/mmbiz_png/cia3tMb8xpGJSuySfict4E8oUlkK2307gQnOiaa93h83mib5Wcp3oKp570jgcWdSBiatwvU9nT2Hgs9Qzg0tvJUU8sw/0?wx_fmt=png\" class=\"inner_link_account_avatar\"> <strong class=\"inner_link_account_nickname\">蚂蚁金服花呗</strong> <i class=\"inner_link_account_wechat\">微信号：mayi_huabei</i></div> <div class=\"weui-desktop-vm_default inner_link_account_type\">服务号</div></li><li class=\"inner_link_account_item\"><div class=\"weui-desktop-vm_primary\"><img src=\"http://mmbiz.qpic.cn/mmbiz_png/yUzZJ4Kn9SKeM7PkPzJ6jS8wPkThrvyOVQBjicHibpSl2u750orYFhBoSOdmIyickfbhCxh26Urz5mudArvImicVLw/0?wx_fmt=png\" class=\"inner_link_account_avatar\"> <strong class=\"inner_link_account_nickname\">蚂蚁金服客户中心</strong> <i class=\"inner_link_account_wechat\">微信号：AlipayCustomerCenter</i></div> <div class=\"weui-desktop-vm_default inner_link_account_type\">订阅号</div></li>\n"
     ]
    }
   ],
   "source": [
    "element = driver.find_element_by_xpath('//ul[@class=\"inner_link_account_list\"]')\n",
    "main_content = element.get_attribute('innerHTML')\n",
    "print(main_content)\n",
    "公众号SERP = main_content\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 455,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 解析\n",
    "root = fromstring(公众号SERP) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 456,
   "metadata": {},
   "outputs": [],
   "source": [
    "主 = root.xpath('//li[@class=\"inner_link_account_item\"]')\n",
    "\n",
    "account_list = []\n",
    "for e in 主:\n",
    "    account_nickname = e.xpath('./div/strong[@class=\"inner_link_account_nickname\"]')[0].text\n",
    "    account_wechat = e.xpath('./div/i[@class=\"inner_link_account_wechat\"]')[0].text\n",
    "    account_img = e.xpath('./div/img/@src')[0]\n",
    "    account = {\"nickname\": account_nickname, \"wechat\": account_wechat, \"img\": account_img,}\n",
    "    account_list.append(account)\n",
    "\n",
    "df_account = pd.DataFrame(account_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 457,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>nickname</th>\n",
       "      <th>wechat</th>\n",
       "      <th>img</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>蚂蚁金服科技</td>\n",
       "      <td>微信号：Ant-Techfin</td>\n",
       "      <td>http://mmbiz.qpic.cn/mmbiz_png/8zGb2W5DJWvF1ib...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>蚂蚁金服</td>\n",
       "      <td>微信号：mayi-jinfu</td>\n",
       "      <td>http://mmbiz.qpic.cn/mmbiz_png/rS76otFH2pNlicV...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>山东蚂蚁金服网络科技有限公司</td>\n",
       "      <td>微信号：SDMYJF_FWH</td>\n",
       "      <td>http://mmbiz.qpic.cn/mmbiz_png/ibouibdiaMA9Kyn...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>蚂蚁金服花呗</td>\n",
       "      <td>微信号：mayi_huabei</td>\n",
       "      <td>http://mmbiz.qpic.cn/mmbiz_png/cia3tMb8xpGJSuy...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>蚂蚁金服客户中心</td>\n",
       "      <td>微信号：AlipayCustomerCenter</td>\n",
       "      <td>http://mmbiz.qpic.cn/mmbiz_png/yUzZJ4Kn9SKeM7P...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         nickname                    wechat  \\\n",
       "0          蚂蚁金服科技           微信号：Ant-Techfin   \n",
       "1            蚂蚁金服            微信号：mayi-jinfu   \n",
       "2  山东蚂蚁金服网络科技有限公司            微信号：SDMYJF_FWH   \n",
       "3          蚂蚁金服花呗           微信号：mayi_huabei   \n",
       "4        蚂蚁金服客户中心  微信号：AlipayCustomerCenter   \n",
       "\n",
       "                                                 img  \n",
       "0  http://mmbiz.qpic.cn/mmbiz_png/8zGb2W5DJWvF1ib...  \n",
       "1  http://mmbiz.qpic.cn/mmbiz_png/rS76otFH2pNlicV...  \n",
       "2  http://mmbiz.qpic.cn/mmbiz_png/ibouibdiaMA9Kyn...  \n",
       "3  http://mmbiz.qpic.cn/mmbiz_png/cia3tMb8xpGJSuy...  \n",
       "4  http://mmbiz.qpic.cn/mmbiz_png/yUzZJ4Kn9SKeM7P...  "
      ]
     },
     "execution_count": 457,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_account"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 458,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<div class=\"weui-desktop-vm_primary\"><img src=\"http://mmbiz.qpic.cn/mmbiz_png/8zGb2W5DJWvF1ibic8Ff0hib2zM1PNggypmobkam18vP1Nh7kaBU77zrJ8WD9vd8nj4uicvXIFXu1Ek0ScZGia91Clw/0?wx_fmt=png\" class=\"inner_link_account_avatar\"> <strong class=\"inner_link_account_nickname\">蚂蚁金服科技</strong> <i class=\"inner_link_account_wechat\">微信号：Ant-Techfin</i></div> <div class=\"weui-desktop-vm_default inner_link_account_type\">订阅号</div>\n"
     ]
    }
   ],
   "source": [
    "element = driver.find_element_by_xpath('//ul[@class=\"inner_link_account_list\"]/li')\n",
    "main_content = element.get_attribute('innerHTML')\n",
    "print(main_content)\n",
    "element.click()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 297,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 跳转testing\n",
    "'''\n",
    "跳转_input = driver.find_element_by_xpath('//span[@class=\"weui-desktop-pagination__form\"]/input')\n",
    "跳转_a = driver.find_element_by_xpath('//span[@class=\"weui-desktop-pagination__form\"]/a')\n",
    "跳转_input.clear()\n",
    "跳转_input.send_keys(2)\n",
    "跳转_a.click()\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 459,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[1, 98]\n",
      "False\n"
     ]
    }
   ],
   "source": [
    "# 跳转上限\n",
    "l_e = driver.find_elements_by_xpath('//label[@class=\"weui-desktop-pagination__num\"]')\n",
    "l_e_int  = [int(x.text) for x in l_e] \n",
    "print (l_e_int)\n",
    "print (l_e_int[0]==l_e_int[-1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 460,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98]\n"
     ]
    }
   ],
   "source": [
    "pages = list(range(l_e_int[0],l_e_int[-1]+1 ))\n",
    "#print(pages[0:2])\n",
    "pages = list(range(1,l_e_int[-1]+1 ))\n",
    "print(pages)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 循环/遍历"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 461,
   "metadata": {},
   "outputs": [],
   "source": [
    "# global varialbes \n",
    "html_raw = dict()\n",
    "main_content =\"\"\n",
    "element = None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 462,
   "metadata": {},
   "outputs": [],
   "source": [
    "def process_pages (pages):\n",
    "    for p in pages:\n",
    "        print (p,end='\\t')\n",
    "\n",
    "        跳转_input = driver.find_element_by_xpath('//span[@class=\"weui-desktop-pagination__form\"]/input')\n",
    "        跳转_a = driver.find_element_by_xpath('//span[@class=\"weui-desktop-pagination__form\"]/a')\n",
    "        跳转_input.clear()\n",
    "        跳转_input.send_keys(p)\n",
    "        跳转_a.click()\n",
    "\n",
    "        time.sleep(45+120*random())\n",
    "\n",
    "        element = driver.find_element_by_xpath('//div[@class=\"inner_link_article_list\"]')\n",
    "        main_content = element.get_attribute('innerHTML')\n",
    "        #print(main_content)\n",
    "        html_raw[p] = main_content"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1\t2\t3\t4\t5\t6\t7\t8\t9\t10\t11\t12\t13\t14\t15\t16\t17\t18\t19\t20\t21\t22\t23\t24\t25\t26\t27\t28\t29\t30\t31\t32\t33\t34\t35\t36\t37\t38\t39\t40\t41\t42\t43\t44\t45\t46\t47\t48\t49\t50\t51\t52\t53\t54\t55\t"
     ]
    }
   ],
   "source": [
    "process_pages(pages)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 465,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "160"
      ]
     },
     "execution_count": 465,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# current\n",
    "p"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 466,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>html_snippets</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>98 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                        html_snippets\n",
       "1   <div><label class=\"inner_link_article_item\"><s...\n",
       "2   <div><label class=\"inner_link_article_item\"><s...\n",
       "3   <div><label class=\"inner_link_article_item\"><s...\n",
       "4   <div><label class=\"inner_link_article_item\"><s...\n",
       "5   <div><label class=\"inner_link_article_item\"><s...\n",
       "..                                                ...\n",
       "94  <div><label class=\"inner_link_article_item\"><s...\n",
       "95  <div><label class=\"inner_link_article_item\"><s...\n",
       "96  <div><label class=\"inner_link_article_item\"><s...\n",
       "97  <div><label class=\"inner_link_article_item\"><s...\n",
       "98  <div><label class=\"inner_link_article_item\"><s...\n",
       "\n",
       "[98 rows x 1 columns]"
      ]
     },
     "execution_count": 466,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame([html_raw]).T\n",
    "df.columns = [\"html_snippets\"]\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 467,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Stored 'html_raw' (dict)\n"
     ]
    }
   ],
   "source": [
    "%store html_raw\n",
    "import pickle \n",
    "filehandler = open(\"html_raw\", 'wb') \n",
    "pickle.dump(html_raw, filehandler)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 468,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "81\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>html_snippets</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>84</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>85</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>87</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>89</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>91</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>92</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                        html_snippets\n",
       "12  <div><label class=\"inner_link_article_item\"><s...\n",
       "83  <div><label class=\"inner_link_article_item\"><s...\n",
       "84  <div><label class=\"inner_link_article_item\"><s...\n",
       "85  <div><label class=\"inner_link_article_item\"><s...\n",
       "86  <div><label class=\"inner_link_article_item\"><s...\n",
       "87  <div><label class=\"inner_link_article_item\"><s...\n",
       "88  <div><label class=\"inner_link_article_item\"><s...\n",
       "89  <div><label class=\"inner_link_article_item\"><s...\n",
       "90  <div><label class=\"inner_link_article_item\"><s...\n",
       "91  <div><label class=\"inner_link_article_item\"><s...\n",
       "92  <div><label class=\"inner_link_article_item\"><s...\n",
       "93  <div><label class=\"inner_link_article_item\"><s...\n",
       "94  <div><label class=\"inner_link_article_item\"><s...\n",
       "95  <div><label class=\"inner_link_article_item\"><s...\n",
       "96  <div><label class=\"inner_link_article_item\"><s...\n",
       "97  <div><label class=\"inner_link_article_item\"><s...\n",
       "98  <div><label class=\"inner_link_article_item\"><s..."
      ]
     },
     "execution_count": 468,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_out = df[~df.duplicated()]\n",
    "print (len(df_out))\n",
    "df[df.duplicated()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 469,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[12, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98]\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[12, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98]"
      ]
     },
     "execution_count": 469,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "try_again = list(df[df.duplicated()].index)\n",
    "print(try_again)\n",
    "try_again = try_again + list (set(pages).difference(set(df.index.values)))\n",
    "try_again"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 暂存档"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 470,
   "metadata": {},
   "outputs": [],
   "source": [
    "filename = fn [\"output\"] [\"公众号_htm_snippets\"] \n",
    "df_out.to_csv(filename.format(公众号=公众号), sep=\"\\t\", encoding=\"utf8\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 471,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "5,5,5,5,5,5,5,5,5,5,5,5,5,5,5,5,5,5,7,5,5,5,5,5,8,5,6,5,5,5,5,5,5,5,5,5,5,5,5,5,6,5,5,5,5,5,5,5,5,5,5,6,9,6,5,6,5,5,7,6,6,5,5,7,15,10,8,8,7,8,9,5,5,5,5,5,6,6,6,7,6,5,5,5,5,5,5,5,5,5,5,5,5,5,5,5,5,5,"
     ]
    }
   ],
   "source": [
    "def parse_html_snippets(_snippet_):\n",
    "    root = fromstring(_snippet_) \n",
    "    title = [x.text for x in root.xpath('//div[@class=\"inner_link_article_title\"]')]\n",
    "    create_time = [x.text for x in root.xpath('//div[@class=\"inner_link_article_date\"]')]\n",
    "    link = [x for x in root.xpath('//a/@href')]\n",
    "    _df_ = pd.DataFrame({\"title\":title, \"create_time\": create_time, \"link\":link})\n",
    "    return(_df_)\n",
    "    \n",
    "l_df = []\n",
    "for p in pages:\n",
    "    _df_ = parse_html_snippets(df.loc[p,\"html_snippets\"])\n",
    "    print (len(_df_), end=\",\")\n",
    "    l_df.append(_df_)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 472,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "      <th>create_time</th>\n",
       "      <th>link</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>支付宝研究员兼OceanBase总架构师杨传辉：我在数据库梦之队的十年成长路</td>\n",
       "      <td>2020-04-27</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>【送书】900页阿里文娱技术精选、覆盖8大技术线， 一览智慧文娱精华！</td>\n",
       "      <td>2020-04-26</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>含ppt下载 | 蚂蚁金服凭什么发布全国首个共享智能联盟标准？</td>\n",
       "      <td>2020-04-24</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>重磅首发！免费下载！《Java 开发手册》泰山版王者归来！</td>\n",
       "      <td>2020-04-22</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020支付宝春招大盘点：技术人，你五行缺蚂蚁</td>\n",
       "      <td>2020-04-19</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>在家办公3天后，我们有这些经验可以和你们分享</td>\n",
       "      <td>2020-02-06</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>支付宝旗下知识管理工具语雀免费开放，助力企业在家办公</td>\n",
       "      <td>2020-02-04</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>@武汉人民，请收好这份名单</td>\n",
       "      <td>2020-01-28</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>守护前线医护英雄！请转告他们来支付宝里领「健康保障金」</td>\n",
       "      <td>2020-01-27</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>驰援武汉，就这么干了！</td>\n",
       "      <td>2020-01-25</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>校招季丨资深专家王维强：服务12亿用户的支付宝需要怎样的安全人才？</td>\n",
       "      <td>2020-04-09</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     title create_time  \\\n",
       "0   支付宝研究员兼OceanBase总架构师杨传辉：我在数据库梦之队的十年成长路  2020-04-27   \n",
       "1      【送书】900页阿里文娱技术精选、覆盖8大技术线， 一览智慧文娱精华！  2020-04-26   \n",
       "2          含ppt下载 | 蚂蚁金服凭什么发布全国首个共享智能联盟标准？  2020-04-24   \n",
       "3            重磅首发！免费下载！《Java 开发手册》泰山版王者归来！  2020-04-22   \n",
       "4                  2020支付宝春招大盘点：技术人，你五行缺蚂蚁  2020-04-19   \n",
       "5                   在家办公3天后，我们有这些经验可以和你们分享  2020-02-06   \n",
       "6               支付宝旗下知识管理工具语雀免费开放，助力企业在家办公  2020-02-04   \n",
       "7                            @武汉人民，请收好这份名单  2020-01-28   \n",
       "8              守护前线医护英雄！请转告他们来支付宝里领「健康保障金」  2020-01-27   \n",
       "9                              驰援武汉，就这么干了！  2020-01-25   \n",
       "10       校招季丨资深专家王维强：服务12亿用户的支付宝需要怎样的安全人才？  2020-04-09   \n",
       "\n",
       "                                                 link  \n",
       "0   http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "1   http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "2   http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "3   http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "4   http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "5   http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "6   http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "7   http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "8   http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "9   http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "10  http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  "
      ]
     },
     "execution_count": 472,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_url_out = pd.concat(l_df).reset_index(drop=True)\n",
    "df_url_out.loc[0:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 473,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "      <th>create_time</th>\n",
       "      <th>link</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>541</th>\n",
       "      <td>蚂蚁金服黑科技：SOFA DTX分布式事务，保障亿级资金操作一致性</td>\n",
       "      <td>2018-05-23</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>542</th>\n",
       "      <td>深度 | 蚂蚁金服DASFAA论文带你深入了解GBDT模型</td>\n",
       "      <td>2018-05-22</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>543</th>\n",
       "      <td>广州市金融局携手蚂蚁金服在防控金融风险领域展开合作</td>\n",
       "      <td>2018-05-21</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>544</th>\n",
       "      <td>蚂蚁金服刘伟光：抓住未来金融科技六大发展趋势</td>\n",
       "      <td>2018-05-18</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>545</th>\n",
       "      <td>干货| 支付系统如何进行分布式改造</td>\n",
       "      <td>2018-05-17</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 title create_time  \\\n",
       "541  蚂蚁金服黑科技：SOFA DTX分布式事务，保障亿级资金操作一致性  2018-05-23   \n",
       "542      深度 | 蚂蚁金服DASFAA论文带你深入了解GBDT模型  2018-05-22   \n",
       "543          广州市金融局携手蚂蚁金服在防控金融风险领域展开合作  2018-05-21   \n",
       "544             蚂蚁金服刘伟光：抓住未来金融科技六大发展趋势  2018-05-18   \n",
       "545                  干货| 支付系统如何进行分布式改造  2018-05-17   \n",
       "\n",
       "                                                  link  \n",
       "541  http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "542  http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "543  http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "544  http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "545  http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  "
      ]
     },
     "execution_count": 473,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_url_out.tail(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 475,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "      <th>create_time</th>\n",
       "      <th>link</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>value</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>【送书】900页阿里文娱技术精选、覆盖8大技术线， 一览智慧文娱精华！</td>\n",
       "      <td>2020-04-26</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>重磅首发！免费下载！《Java 开发手册》泰山版王者归来！</td>\n",
       "      <td>2020-04-22</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020支付宝春招大盘点：技术人，你五行缺蚂蚁</td>\n",
       "      <td>2020-04-19</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>在家办公3天后，我们有这些经验可以和你们分享</td>\n",
       "      <td>2020-02-06</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>校招季丨资深专家王维强：服务12亿用户的支付宝需要怎样的安全人才？</td>\n",
       "      <td>2020-04-09</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>540</th>\n",
       "      <td>干货| 支付系统如何进行分布式改造</td>\n",
       "      <td>2018-05-17</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>541</th>\n",
       "      <td>蚂蚁金服黑科技：SOFA DTX分布式事务，保障亿级资金操作一致性</td>\n",
       "      <td>2018-05-23</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>542</th>\n",
       "      <td>深度 | 蚂蚁金服DASFAA论文带你深入了解GBDT模型</td>\n",
       "      <td>2018-05-22</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>543</th>\n",
       "      <td>广州市金融局携手蚂蚁金服在防控金融风险领域展开合作</td>\n",
       "      <td>2018-05-21</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>545</th>\n",
       "      <td>干货| 支付系统如何进行分布式改造</td>\n",
       "      <td>2018-05-17</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>343 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     title create_time  \\\n",
       "value                                                    \n",
       "1      【送书】900页阿里文娱技术精选、覆盖8大技术线， 一览智慧文娱精华！  2020-04-26   \n",
       "3            重磅首发！免费下载！《Java 开发手册》泰山版王者归来！  2020-04-22   \n",
       "4                  2020支付宝春招大盘点：技术人，你五行缺蚂蚁  2020-04-19   \n",
       "5                   在家办公3天后，我们有这些经验可以和你们分享  2020-02-06   \n",
       "10       校招季丨资深专家王维强：服务12亿用户的支付宝需要怎样的安全人才？  2020-04-09   \n",
       "...                                    ...         ...   \n",
       "540                      干货| 支付系统如何进行分布式改造  2018-05-17   \n",
       "541      蚂蚁金服黑科技：SOFA DTX分布式事务，保障亿级资金操作一致性  2018-05-23   \n",
       "542          深度 | 蚂蚁金服DASFAA论文带你深入了解GBDT模型  2018-05-22   \n",
       "543              广州市金融局携手蚂蚁金服在防控金融风险领域展开合作  2018-05-21   \n",
       "545                      干货| 支付系统如何进行分布式改造  2018-05-17   \n",
       "\n",
       "                                                    link  \n",
       "value                                                     \n",
       "1      http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "3      http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "4      http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "5      http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "10     http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "...                                                  ...  \n",
       "540    http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "541    http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "542    http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "543    http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "545    http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "\n",
       "[343 rows x 3 columns]"
      ]
     },
     "execution_count": 475,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# tagging 标记\n",
    "tagging_list = [\"\",\"马化腾\", \"腾讯\", \"微众银行\",\"腾讯复星\",\"腾讯风控\",\"腾讯支付\",\"WeChat\",\"We Remit\",\\\n",
    "                \"公益日\",\"红包\",\\\n",
    "                \"腾讯财付通\",\"鹅厂\",\"QQ钱包\",\"QQ红包\",\"QQ\",\\\n",
    "                \"只有一\",\"大咖\",\"听说\",\"图片\",\"照片\",\"小编\",\\\n",
    "                \"洗钱\", \"黑产\",\"被骗\",\"腾讯安全课\",\"诈骗\", \"炒股\",\"神秘兼职\",\"神秘组织\",\\\n",
    "                \"财付通\", \"品牌\",\\\n",
    "                \"收款\",\\\n",
    "                \"报告\",\\\n",
    "                \"银行卡\",\"理财\",\"选股\",\"发票\",\"基金\",\\\n",
    "                \"区块链\",\"金融云\",\"O2O\",\"农产品\",\"家乡\",\\\n",
    "                \"数据\", \"数据赋能\", \"智能\", \"数字孪生\", \"智慧大脑\",\\\n",
    "                \"出行\",\"乘车\",\"公交\",\"乘车码\", \"智慧地铁\",\\\n",
    "                \"高峰论坛\", \"智库\",\\\n",
    "                \"央行\",\"新规\", \\\n",
    "                \"微信\", \"微信支付\", \"跨境支付\", \"移动支付\",\"非银行支付\",\"电子支付\",\\\n",
    "                \"互联网金融\", \"金融科技\",\"互联网＋\",\"互联网+金融\",\"普惠金融\",\"虚拟银行\",\\\n",
    "                \"开放\",\"生态\",\"复杂\",\"互联网思维\",\"全球合作伙伴\",\\\n",
    "                \"联合国\", \"城市\", \"粤港澳大湾区\", \"平台\", \"可持续发展\", \"未来\", \"绿色\",\\\n",
    "                \"医护\",\"防护服\",\"小时\",\"武汉\",\"危机\",\"新冠肺炎\", \"疫\", \"疫情\", \"复工\",\"停课\",\"宅经济\",\\\n",
    "                \"基建\",\"新基建\"] #overwritable\n",
    "\n",
    "v_v_list = []\n",
    "\n",
    "for tag in tagging_list:\n",
    "    index_list = df_url_out [ df_url_out.title.str.contains(tag) ].index.to_list()\n",
    "    v_v_pairs = pd.DataFrame({tag:index_list}).melt().set_index(\"value\")\n",
    "    v_v_list.append(v_v_pairs)\n",
    "\n",
    "df_cat = v_v_list[0]\n",
    "for d in v_v_list:\n",
    "    df_cat.update(d)\n",
    "    \n",
    "# 尚未标记内容\n",
    "df_url_out.loc [ df_cat.query('variable==\"\"').index ]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 476,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw==&mid=2247490520&idx=1&sn=81d8dd92ac1344ccbc49de5efe87bbd6&chksm=e9aba3a8dedc2abe03d64bac4fbe0a74a8e1834f7bca9d440d0767d4d5ec4bf303b5a2fbf9ab#rd'"
      ]
     },
     "execution_count": 476,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_url_out.loc[53].link"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 477,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "      <th>create_time</th>\n",
       "      <th>link</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>55</th>\n",
       "      <td>在家办公3天后，我们有这些经验可以和你们分享</td>\n",
       "      <td>2020-02-06</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56</th>\n",
       "      <td>支付宝旗下知识管理工具语雀免费开放，助力企业在家办公</td>\n",
       "      <td>2020-02-04</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57</th>\n",
       "      <td>@武汉人民，请收好这份名单</td>\n",
       "      <td>2020-01-28</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58</th>\n",
       "      <td>守护前线医护英雄！请转告他们来支付宝里领「健康保障金」</td>\n",
       "      <td>2020-01-27</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59</th>\n",
       "      <td>驰援武汉，就这么干了！</td>\n",
       "      <td>2020-01-25</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>541</th>\n",
       "      <td>蚂蚁金服黑科技：SOFA DTX分布式事务，保障亿级资金操作一致性</td>\n",
       "      <td>2018-05-23</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>542</th>\n",
       "      <td>深度 | 蚂蚁金服DASFAA论文带你深入了解GBDT模型</td>\n",
       "      <td>2018-05-22</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>543</th>\n",
       "      <td>广州市金融局携手蚂蚁金服在防控金融风险领域展开合作</td>\n",
       "      <td>2018-05-21</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>544</th>\n",
       "      <td>蚂蚁金服刘伟光：抓住未来金融科技六大发展趋势</td>\n",
       "      <td>2018-05-18</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>545</th>\n",
       "      <td>干货| 支付系统如何进行分布式改造</td>\n",
       "      <td>2018-05-17</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>85 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 title create_time  \\\n",
       "55              在家办公3天后，我们有这些经验可以和你们分享  2020-02-06   \n",
       "56          支付宝旗下知识管理工具语雀免费开放，助力企业在家办公  2020-02-04   \n",
       "57                       @武汉人民，请收好这份名单  2020-01-28   \n",
       "58         守护前线医护英雄！请转告他们来支付宝里领「健康保障金」  2020-01-27   \n",
       "59                         驰援武汉，就这么干了！  2020-01-25   \n",
       "..                                 ...         ...   \n",
       "541  蚂蚁金服黑科技：SOFA DTX分布式事务，保障亿级资金操作一致性  2018-05-23   \n",
       "542      深度 | 蚂蚁金服DASFAA论文带你深入了解GBDT模型  2018-05-22   \n",
       "543          广州市金融局携手蚂蚁金服在防控金融风险领域展开合作  2018-05-21   \n",
       "544             蚂蚁金服刘伟光：抓住未来金融科技六大发展趋势  2018-05-18   \n",
       "545                  干货| 支付系统如何进行分布式改造  2018-05-17   \n",
       "\n",
       "                                                  link  \n",
       "55   http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "56   http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "57   http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "58   http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "59   http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "..                                                 ...  \n",
       "541  http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "542  http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "543  http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "544  http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "545  http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "\n",
       "[85 rows x 3 columns]"
      ]
     },
     "execution_count": 477,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_url_out[df_url_out.duplicated()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 478,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "      <th>create_time</th>\n",
       "      <th>link</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>支付宝研究员兼OceanBase总架构师杨传辉：我在数据库梦之队的十年成长路</td>\n",
       "      <td>2020-04-27</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>【送书】900页阿里文娱技术精选、覆盖8大技术线， 一览智慧文娱精华！</td>\n",
       "      <td>2020-04-26</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>含ppt下载 | 蚂蚁金服凭什么发布全国首个共享智能联盟标准？</td>\n",
       "      <td>2020-04-24</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>重磅首发！免费下载！《Java 开发手册》泰山版王者归来！</td>\n",
       "      <td>2020-04-22</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020支付宝春招大盘点：技术人，你五行缺蚂蚁</td>\n",
       "      <td>2020-04-19</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>461</th>\n",
       "      <td>蚂蚁金服黑科技：SOFA DTX分布式事务，保障亿级资金操作一致性</td>\n",
       "      <td>2018-05-23</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>462</th>\n",
       "      <td>深度 | 蚂蚁金服DASFAA论文带你深入了解GBDT模型</td>\n",
       "      <td>2018-05-22</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>463</th>\n",
       "      <td>广州市金融局携手蚂蚁金服在防控金融风险领域展开合作</td>\n",
       "      <td>2018-05-21</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>464</th>\n",
       "      <td>蚂蚁金服刘伟光：抓住未来金融科技六大发展趋势</td>\n",
       "      <td>2018-05-18</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>465</th>\n",
       "      <td>干货| 支付系统如何进行分布式改造</td>\n",
       "      <td>2018-05-17</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>461 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                      title create_time  \\\n",
       "0    支付宝研究员兼OceanBase总架构师杨传辉：我在数据库梦之队的十年成长路  2020-04-27   \n",
       "1       【送书】900页阿里文娱技术精选、覆盖8大技术线， 一览智慧文娱精华！  2020-04-26   \n",
       "2           含ppt下载 | 蚂蚁金服凭什么发布全国首个共享智能联盟标准？  2020-04-24   \n",
       "3             重磅首发！免费下载！《Java 开发手册》泰山版王者归来！  2020-04-22   \n",
       "4                   2020支付宝春招大盘点：技术人，你五行缺蚂蚁  2020-04-19   \n",
       "..                                      ...         ...   \n",
       "461       蚂蚁金服黑科技：SOFA DTX分布式事务，保障亿级资金操作一致性  2018-05-23   \n",
       "462           深度 | 蚂蚁金服DASFAA论文带你深入了解GBDT模型  2018-05-22   \n",
       "463               广州市金融局携手蚂蚁金服在防控金融风险领域展开合作  2018-05-21   \n",
       "464                  蚂蚁金服刘伟光：抓住未来金融科技六大发展趋势  2018-05-18   \n",
       "465                       干货| 支付系统如何进行分布式改造  2018-05-17   \n",
       "\n",
       "                                                  link  \n",
       "0    http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "1    http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "2    http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "3    http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "4    http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "..                                                 ...  \n",
       "461  http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "462  http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "463  http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "464  http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "465  http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "\n",
       "[461 rows x 3 columns]"
      ]
     },
     "execution_count": 478,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_url_out[~df_url_out.duplicated()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 479,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "      <th>create_time</th>\n",
       "      <th>link</th>\n",
       "      <th>variable</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>支付宝研究员兼OceanBase总架构师杨传辉：我在数据库梦之队的十年成长路</td>\n",
       "      <td>2020-04-27</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "      <td>数据</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>【送书】900页阿里文娱技术精选、覆盖8大技术线， 一览智慧文娱精华！</td>\n",
       "      <td>2020-04-26</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "      <td>无法分类</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>含ppt下载 | 蚂蚁金服凭什么发布全国首个共享智能联盟标准？</td>\n",
       "      <td>2020-04-24</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "      <td>智能</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>重磅首发！免费下载！《Java 开发手册》泰山版王者归来！</td>\n",
       "      <td>2020-04-22</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "      <td>无法分类</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020支付宝春招大盘点：技术人，你五行缺蚂蚁</td>\n",
       "      <td>2020-04-19</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "      <td>无法分类</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>541</th>\n",
       "      <td>蚂蚁金服黑科技：SOFA DTX分布式事务，保障亿级资金操作一致性</td>\n",
       "      <td>2018-05-23</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "      <td>无法分类</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>542</th>\n",
       "      <td>深度 | 蚂蚁金服DASFAA论文带你深入了解GBDT模型</td>\n",
       "      <td>2018-05-22</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "      <td>无法分类</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>543</th>\n",
       "      <td>广州市金融局携手蚂蚁金服在防控金融风险领域展开合作</td>\n",
       "      <td>2018-05-21</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "      <td>无法分类</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>544</th>\n",
       "      <td>蚂蚁金服刘伟光：抓住未来金融科技六大发展趋势</td>\n",
       "      <td>2018-05-18</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "      <td>未来</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>545</th>\n",
       "      <td>干货| 支付系统如何进行分布式改造</td>\n",
       "      <td>2018-05-17</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "      <td>无法分类</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>546 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                      title create_time  \\\n",
       "0    支付宝研究员兼OceanBase总架构师杨传辉：我在数据库梦之队的十年成长路  2020-04-27   \n",
       "1       【送书】900页阿里文娱技术精选、覆盖8大技术线， 一览智慧文娱精华！  2020-04-26   \n",
       "2           含ppt下载 | 蚂蚁金服凭什么发布全国首个共享智能联盟标准？  2020-04-24   \n",
       "3             重磅首发！免费下载！《Java 开发手册》泰山版王者归来！  2020-04-22   \n",
       "4                   2020支付宝春招大盘点：技术人，你五行缺蚂蚁  2020-04-19   \n",
       "..                                      ...         ...   \n",
       "541       蚂蚁金服黑科技：SOFA DTX分布式事务，保障亿级资金操作一致性  2018-05-23   \n",
       "542           深度 | 蚂蚁金服DASFAA论文带你深入了解GBDT模型  2018-05-22   \n",
       "543               广州市金融局携手蚂蚁金服在防控金融风险领域展开合作  2018-05-21   \n",
       "544                  蚂蚁金服刘伟光：抓住未来金融科技六大发展趋势  2018-05-18   \n",
       "545                       干货| 支付系统如何进行分布式改造  2018-05-17   \n",
       "\n",
       "                                                  link variable  \n",
       "0    http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...       数据  \n",
       "1    http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...     无法分类  \n",
       "2    http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...       智能  \n",
       "3    http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...     无法分类  \n",
       "4    http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...     无法分类  \n",
       "..                                                 ...      ...  \n",
       "541  http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...     无法分类  \n",
       "542  http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...     无法分类  \n",
       "543  http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...     无法分类  \n",
       "544  http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...       未来  \n",
       "545  http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...     无法分类  \n",
       "\n",
       "[546 rows x 4 columns]"
      ]
     },
     "execution_count": 479,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_o = df_url_out.join(df_cat).replace(\"\", np.nan).fillna(\"无法分类\")\n",
    "df_o"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 480,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "      <th>create_time</th>\n",
       "      <th>link</th>\n",
       "      <th>variable</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [title, create_time, link, variable]\n",
       "Index: []"
      ]
     },
     "execution_count": 480,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_o[df_o.title.str.contains(\"腾讯\")]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 481,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>variable</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>无法分类</th>\n",
       "      <td>343</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>数据</th>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>开放</th>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>未来</th>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>平台</th>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>智能</th>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>区块链</th>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>金融科技</th>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>生态</th>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>城市</th>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>武汉</th>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>小时</th>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>疫情</th>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>普惠金融</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>红包</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>疫</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>基金</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>医护</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>移动支付</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>听说</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>智库</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>图片</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>出行</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>报告</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>全球合作伙伴</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          title\n",
       "variable       \n",
       "无法分类        343\n",
       "数据           40\n",
       "开放           29\n",
       "未来           25\n",
       "平台           18\n",
       "智能           17\n",
       "区块链          16\n",
       "金融科技         13\n",
       "生态            9\n",
       "城市            5\n",
       "武汉            5\n",
       "小时            4\n",
       "疫情            4\n",
       "普惠金融          3\n",
       "红包            2\n",
       "疫             2\n",
       "基金            2\n",
       "医护            2\n",
       "移动支付          1\n",
       "听说            1\n",
       "智库            1\n",
       "图片            1\n",
       "出行            1\n",
       "报告            1\n",
       "全球合作伙伴        1"
      ]
     },
     "execution_count": 481,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_stats = df_o.groupby(by=\"variable\").agg({\"title\":\"count\"}).sort_values(by=\"title\", ascending=False)\n",
    "df_stats"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 输出"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 444,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_account.columns.name = \"rel_accounts\"\n",
    "df_o.columns.name = \"url_cat\"\n",
    "df_stats.columns.name = \"stats\"\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 445,
   "metadata": {},
   "outputs": [],
   "source": [
    "_df_.columns.name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 446,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get the xlsxwriter workbook and worksheet objects.  \n",
    "with pd.ExcelWriter(fn[\"output\"][\"公众号_xlsx\"].format(公众号=公众号)) as writer:\n",
    "    workbook  = writer.book\n",
    "\n",
    "    for _df_ in [df_account, df_o, df_stats]:\n",
    "        _df_.to_excel(writer, sheet_name = _df_.columns.name)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "                  \"公众号_xlsx\": \"data_sets/{公众号}_url.xlsx\" } \\\n",
    " "
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
